USE NutritionTracker
GO
IF EXISTS (SELECT 1 FROM sysobjects WHERE ID = object_id(N'MeasurementUnit_Read_List') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
	DROP PROCEDURE MeasurementUnit_Read_List
GO

CREATE PROC MeasurementUnit_Read_List
(
@MeasurementTypeID TINYINT = NULL,
@MeasurementUnitID TINYINT = NULL,
@MeasurementUnitFullName	NVARCHAR(50) = NULL,
@MinLevel	INT = NULL,
@MaxLevel	INT = NULL
)
AS
BEGIN
	SET NOCOUNT ON

	SELECT		@MeasurementUnitFullName = NULLIF(RTRIM(LTRIM(@MeasurementUnitFullName)),'');

	WITH RecursiveMeasurementUnit
	(
		MeasurementUnitID,
		ParentMeasurementUnitID,
		MeasurementTypeID,
		MeasurementUnitName,
		MeasurementUnitFullName,
		UnitFactor,
		Level
	)
	AS
	(
		SELECT		MeasurementUnit.MeasurementUnitID,
			MeasurementUnit.ParentMeasurementUnitID,
			MeasurementUnit.MeasurementTypeID,
			MeasurementUnit.MeasurementUnitName,
			MeasurementUnit.MeasurementUnitFullName,
			MeasurementUnit.UnitFactor,
			0 AS Level
		FROM		MeasurementUnit	
		WHERE		1 = CASE
						WHEN @MeasurementUnitID IS NULL THEN 	
							CASE
								WHEN MeasurementUnit.ParentMeasurementUnitID IS NULL THEN 1
								ELSE 0
							END
						WHEN MeasurementUnit.MeasurementUnitID = @MeasurementUnitID THEN 1
						ELSE 0
					END
		AND		1 = CASE
						WHEN @MeasurementTypeID IS NULL THEN 1
						WHEN MeasurementUnit.MeasurementTypeID = @MeasurementTypeID THEN 1
						ELSE 0
					END
		UNION ALL
		SELECT		MeasurementUnit.MeasurementUnitID,
			MeasurementUnit.ParentMeasurementUnitID,
			MeasurementUnit.MeasurementTypeID,
			MeasurementUnit.MeasurementUnitName,
			MeasurementUnit.MeasurementUnitFullName,
			MeasurementUnit.UnitFactor,
			Level + 1
		FROM	MeasurementUnit
		INNER JOIN RecursiveMeasurementUnit
			ON MeasurementUnit.ParentMeasurementUnitID = RecursiveMeasurementUnit.MeasurementUnitID
	)

	SELECT		MeasurementUnitID,
			ParentMeasurementUnitID,
			MeasurementTypeID,
			MeasurementUnitName,
			MeasurementUnitFullName,
			UnitFactor,
			Level
	FROM		RecursiveMeasurementUnit
	WHERE		1 =	CASE
						WHEN @MeasurementUnitFullName IS NULL THEN 1
						WHEN MeasurementUnitFullName LIKE '%' + @MeasurementUnitFullName + '%' THEN 1
						ELSE 0
					END
	AND		1 =	CASE
					WHEN @MinLevel IS NULL  THEN 1
					WHEN Level >= @MinLevel THEN 1
					ELSE 0
				END
	AND		1 =	CASE
					WHEN @MaxLevel IS NULL THEN 1
					WHEN Level <= @MaxLevel THEN 1
					ELSE 0
				END
	ORDER BY		MeasurementTypeID
	
	SET NOCOUNT OFF
END
GO
GRANT EXECUTE ON MeasurementUnit_Read_List TO NUTRKR_WEB
GO